local ssi_medicaid "N:/Project/50732_RDRC/DC1/~51702/BC23-TBD (SSI Medicaid)/Data"

import excel using "`ssi_medicaid'/Output/SSI Status Verification/_00_ssi_asr_counts.xlsx", first clear

ren B ssi_2017
ren C ssi_2018
ren D ssi_2019

ren State state

statastates, name(state)

drop state state_fips _merge
ren state_abbrev state
tempfile ssi_counts
save "`ssi_counts'", replace

import excel using "`ssi_medicaid'/Output/SSI Status Verification/_100_ssi_elig_counts.xlsx", first clear
rename STATE_CD state 
merge 1:1 state using "`ssi_counts'"
assert inlist(state,"PR","VI") if _merge == 1
keep if _merge == 3
drop _merge

foreach yr of numlist 2017/2019 {
	foreach var in elig_code ssi_flag ssi_status any_ssi{
		gen `var'_`yr'_ratio = count_`var'_`yr'/ssi_`yr'
	}
}

// SSI States: AK, ID, KS, NE, NV, OR, UT
// 209B States: CT, HI, IL, MN, MO, NH, ND, OK, VA

gen state_ssi = inlist(state,"AK","ID","KS","NE","NV","OR","UT")
gen state_209b = inlist(state,"CT","HI","IL","MN","MO","NH","ND","OK","VA")

gen good_state = 0
gen ssi_id = ""
// States within 10% in all years on using eligibility codes
tab state if inrange(elig_code_2017_ratio,0.9,1.1) & inrange(elig_code_2018_ratio,0.9,1.1) & inrange(elig_code_2019_ratio,0.9,1.1) & good_state ~= 1
replace good_state = 1 if inrange(elig_code_2017_ratio,0.9,1.1) & inrange(elig_code_2018_ratio,0.9,1.1) & inrange(elig_code_2019_ratio,0.9,1.1) & good_state ~= 1
replace ssi_id = "ELIG CODE" if inrange(elig_code_2017_ratio,0.9,1.1) & inrange(elig_code_2018_ratio,0.9,1.1) & inrange(elig_code_2019_ratio,0.9,1.1) & ssi_id == ""

// States within 10% in all years using the SSI Flag 
tab state if inrange(ssi_flag_2017_ratio,0.9,1.1) & inrange(ssi_flag_2018_ratio,0.9,1.1) & inrange(ssi_flag_2019_ratio,0.9,1.1) & good_state ~= 1
replace good_state = 1 if inrange(ssi_flag_2017_ratio,0.9,1.1) & inrange(ssi_flag_2018_ratio,0.9,1.1) & inrange(ssi_flag_2019_ratio,0.9,1.1) & good_state ~= 1
replace ssi_id = "SSI FLAG" if inrange(ssi_flag_2017_ratio,0.9,1.1) & inrange(ssi_flag_2018_ratio,0.9,1.1) & inrange(ssi_flag_2019_ratio,0.9,1.1) & ssi_id == ""

// States within 10% in all years using the SSI Status
tab state if inrange(ssi_status_2017_ratio,0.9,1.1) & inrange(ssi_status_2018_ratio,0.9,1.1) & inrange(ssi_status_2019_ratio,0.9,1.1) & good_state ~= 1
replace good_state = 1 if inrange(ssi_status_2017_ratio,0.9,1.1) & inrange(ssi_status_2018_ratio,0.9,1.1) & inrange(ssi_status_2019_ratio,0.9,1.1) & good_state ~= 1
replace ssi_id = "SSI STATUS" if inrange(ssi_status_2017_ratio,0.9,1.1) & inrange(ssi_status_2018_ratio,0.9,1.1) & inrange(ssi_status_2019_ratio,0.9,1.1) & ssi_id == ""

// States are within 10% in all years on the "any" flag
tab state if inrange(any_ssi_2017_ratio,0.9,1.1) & inrange(any_ssi_2018_ratio,0.9,1.1) & inrange(any_ssi_2019_ratio,0.9,1.1) & good_state ~= 1
replace good_state = 1 if inrange(any_ssi_2017_ratio,0.9,1.1) & inrange(any_ssi_2018_ratio,0.9,1.1) & inrange(any_ssi_2019_ratio,0.9,1.1) & good_state ~= 1
replace ssi_id = "ANY SSI" if inrange(any_ssi_2017_ratio,0.9,1.1) & inrange(any_ssi_2018_ratio,0.9,1.1) & inrange(any_ssi_2019_ratio,0.9,1.1) & ssi_id == ""

// Checks for a few others that might be reliable -- any of them within 5%
gen any_within_5 = 0
foreach yr of numlist 2017/2019 {
foreach var in elig_code ssi_flag ssi_status any_ssi{
	replace any_within_5 = 1 if inrange(`var'_`yr'_ratio,0.95,1.05) & good_state ~= 1
}
}
tab state if any_within_5 == 1

// Checks for a few others that might be reliable -- all within 13%
gen all_within_13 = 0
replace all_within_13 = 1 if inrange(elig_code_2017_ratio,0.87,1.13) & inrange(elig_code_2018_ratio,0.87,1.13) & inrange(elig_code_2019_ratio,0.87,1.13) & good_state ~= 1
replace ssi_id = "ELIG CODE 13" if inrange(elig_code_2017_ratio,0.87,1.13) & inrange(elig_code_2018_ratio,0.87,1.13) & inrange(elig_code_2019_ratio,0.87,1.13) & ssi_id == ""

replace all_within_13 = 1 if inrange(ssi_flag_2017_ratio,0.87,1.13) & inrange(ssi_flag_2018_ratio,0.87,1.13) & inrange(ssi_flag_2019_ratio,0.87,1.13) & good_state ~= 1
replace ssi_id = "SSI FLAG 13" if inrange(ssi_flag_2017_ratio,0.87,1.13) & inrange(ssi_flag_2018_ratio,0.87,1.13) & inrange(ssi_flag_2019_ratio,0.87,1.13) & ssi_id == ""

replace all_within_13 = 1 if inrange(ssi_status_2017_ratio,0.87,1.13) & inrange(ssi_status_2018_ratio,0.87,1.13) & inrange(ssi_status_2019_ratio,0.87,1.13) & good_state ~= 1
replace ssi_id = "SSI STATUS 13" if inrange(ssi_status_2017_ratio,0.87,1.13) & inrange(ssi_status_2018_ratio,0.87,1.13) & inrange(ssi_status_2019_ratio,0.87,1.13) & ssi_id == ""

replace all_within_13 = 1 if inrange(any_ssi_2017_ratio,0.87,1.13) & inrange(any_ssi_2018_ratio,0.87,1.13) & inrange(any_ssi_2019_ratio,0.87,1.13) & good_state ~= 1
tab state if all_within_13 == 1
replace ssi_id = "ANY SSI 13" if inrange(any_ssi_2017_ratio,0.87,1.13) & inrange(any_ssi_2018_ratio,0.87,1.13) & inrange(any_ssi_2019_ratio,0.87,1.13) & ssi_id == ""

replace good_state = 1 if all_within_13 == 1

gen all_within_20 = 0
replace all_within_20 = 1 if inrange(elig_code_2017_ratio,0.80,1.20) & inrange(elig_code_2018_ratio,0.80,1.20) & inrange(elig_code_2019_ratio,0.80,1.20) & good_state ~= 1
replace all_within_20 = 1 if inrange(ssi_flag_2017_ratio,0.80,1.20) & inrange(ssi_flag_2018_ratio,0.80,1.20) & inrange(ssi_flag_2019_ratio,0.80,1.20) & good_state ~= 1
replace all_within_20 = 1 if inrange(ssi_status_2017_ratio,0.80,1.20) & inrange(ssi_status_2018_ratio,0.80,1.20) & inrange(ssi_status_2019_ratio,0.80,1.20) & good_state ~= 1
replace all_within_20 = 1 if inrange(any_ssi_2017_ratio,0.80,1.20) & inrange(any_ssi_2018_ratio,0.80,1.20) & inrange(any_ssi_2019_ratio,0.80,1.20) & good_state ~= 1
tab state if all_within_20 == 1 & all_within_13 == 0

tablist state good_state ssi_id state_209b state_ssi, sort(v)

keep state good_state ssi_id

gen state_ssi = inlist(state,"AK","ID","KS","NE","NV","OR","UT")
gen state_209b = inlist(state,"CT","HI","IL","MN","MO","NH","ND","OK","VA")

gen state_diff = state_ssi == 1 | state_209b == 1

drop ssi_id state_ssi state_209b

export excel using "`ssi_medicaid'/Output/SSI Status Verification/good_states.xlsx", replace firstrow(variables)
